# !/usr/bin/env python
# -*- encoding: utf-8 -*-
# @Author : 大野
from pymysql.converters import escape_string


class SettingSql:
    def add_project(self, project_name, project_code, project_manager, desc):
        '''
        增加项目SQL
        :param service_name: 服务名称
        :return:
        '''
        list = []
        sql = f'INSERT INTO project_info VALUES(NULL,"{project_name}","{project_code}","{project_manager}","{desc}",NOW(),NOW())'
        list.append(sql)
        return list

    def select_project(self):
        """查询项目名称"""
        list = []
        sql = f'select * from project_info'
        list.append(sql)
        return list

    def select_project_id(self, project_id):
        list = []
        sql = f'select * from project_info where id = {project_id}'
        list.append(sql)
        return list

    def select_service_id(self, service_id):
        list = []
        sql = f'select * from project_service where id = {service_id}'
        list.append(sql)
        return list

    def select_version_id(self, version_id):
        list = []
        sql = f'select * from project_version where id = {version_id}'
        list.append(sql)
        return list

    def add_service(self, project_id, service_name, service_desc):
        '''
        增加服务SQL
        :param service_name: 服务名称
        :return:
        '''
        list = []
        sql = f'INSERT INTO project_service VALUES(NULL,{project_id},"{service_name}","{service_desc}",NOW())'
        list.append(sql)
        return list

    def add_version(self, project_id, service_id, version_name, version_manage, version_desc):
        '''
        增加版本SQL
        :param version: 版本
        :param service: 服务
        :return:
        '''
        list = []
        sql = f"INSERT INTO project_version VALUES(NULL,{project_id},{service_id},'{version_name}','{version_manage}','{version_desc}',NOW())"
        list.append(sql)
        return list

    def select_serviceId(self, service):
        '''
        查询服务版本SQL
        :param service:
        :return:
        '''
        list = []
        sql = f"select id from project_service where service='{service}'"
        list.append(sql)
        return list

    def select_service(self, project_id):
        '''
        查询服务版本信息
        :param project_id:
        :return:
        '''
        list = []
        sql = f"select * from project_service where project_id = {project_id}"
        list.append(sql)
        return list

    def updeta_project(self, project_name, project_code, project_manager, desc, project_id):
        '''
        更新项目信息
        :param desc: 项目备注
        :param project_name: 项目名称
        :param project_code: 项目编码
        :param project_manager: 项目负责人
        :param project_id:项目id
        :return:
        '''
        list = []
        sql = f"UPDATE `platform_test`.`project_info` SET `project_name` = '{project_name}', `project_code` = '{project_code}', `project_manager` = '{project_manager}', `desc` = '{desc}',  `modify_time` = NOW() WHERE `id` = {project_id};"
        list.append(sql)
        return list

    def updeta_service(self, service_name, service_desc, service_id):
        '''
        更新服务信息
        :param service_name:
        :param service_id:
        :return:
        '''
        list = []
        sql = f"UPDATE project_service SET `service_name` = '{service_name}',`service_desc` = '{service_desc}' WHERE `id` = {service_id};"
        list.append(sql)
        return list

    def select_version(self, service_id):
        '''
        查询版本
        :param service_id:
        :return:
        '''
        list = []
        sql = f"select * from project_version where service_id = {service_id};"
        list.append(sql)
        return list

    def update_version(self, version_name, version_manage, version_desc, version_id):
        '''
        修改版本号
        :param version_name:
        :param version_manage:
        :param version_desc:
        :param version_id:
        :return:
        '''
        list = []
        sql = f"UPDATE project_version SET `version_name` = '{version_name}', `version_manage` = '{version_manage}', " \
              f"`version_desc` = '{version_desc}' WHERE `id` = {version_id}; "
        list.append(sql)
        return list

    def add_host(self, data):
        header = escape_string(data['header'])
        if data["relation"] == '':
            relation = None
        else:
            relation = data["relation"]
        '''
        增加域名配置
        :param data:
        :return:
        '''
        list = []
        sql = f'''INSERT INTO project_url_mapping ( `project_id`, `case_path_url`, `case_mapping_name`, `tenant`, 
        `relation`,`header`,`email`) VALUES ({data["project_id"]}, "{data["case_path_url"]}", "{data["case_mapping_name"]}", 
        "{data["tenant"]}", "{relation}","{header}","{data["email"]}"); '''
        list.append(sql)
        return list

    def select_host(self, project_id=None):
        """查询host信息"""
        list = []
        if project_id is not None:
            sql = f'select *from project_url_mapping pm,project_info pi where pm.project_id=pi.id and pm.project_id={project_id}'
            list.append(sql)
        else:
            sql = 'select *from project_url_mapping pm,project_info pi where pm.project_id=pi.id'
            list.append(sql)
        return list

    def delete_url_info(self,id):
        """删除host信息"""
        list = []
        sql = f"""delete from project_url_mapping where id ={id};"""
        list.append(sql)
        return list